const express = require('express');
const mysql = require('mysql');
const os = require('os');
const cors = require('cors');
const bodyParser = require('body-parser');

// 获取本机IP地址
const networkInterfaces = os.networkInterfaces();
for (const interfaceName in networkInterfaces) {
    networkInterfaces[interfaceName].forEach((interface) => {
        if (interface.family === 'IPv4' && !interface.internal) {
            console.log(`Interface: ${interfaceName}, IP Address: ${interface.address}`);
        }
    });
}

const app = express();
app.use(bodyParser.json());
app.use(express.json()); // 允许处理JSON格式的请求体数据
app.use(cors()); // 跨域

// 创建连接池
const db = mysql.createPool({
    host: '127.0.0.1',
    user: 'root',
    password: 'hdjz30536',
    database: 'blog'
});

// 测试数据库连接
db.getConnection((err, connection) => {
    if (err) throw err;
    console.log('数据库连接成功');
    connection.release();
});

// 登录或注册
app.post('/login', (req, res) => {
    const { username, password, name, contact, role, messages } = req.body;
    const sql1 = 'SELECT * FROM users WHERE username = ?';
    db.query(sql1, [username], (err, rows) => {
        if (err) {
            console.error('数据查找错误：', err.message);
            return res.status(500).json({ error: 'DB error' });
        }
        if (rows.length === 1) {
            const sql2 = 'SELECT * FROM users WHERE username = ? AND password = ?';
            db.query(sql2, [username, password], (err, rows) => {
                if (err) {
                    console.error('数据验证错误：', err.message);
                    return res.status(500).json({ error: 'DB error' });
                }
                if (rows.length > 0) {
                    console.log('登录成功');
                    return res.status(200).json({ message: '登录成功' });
                } else {
                    console.log('密码错误');
                    return res.status(200).json({ message: '用户名或密码错误' });
                }
            });
        } else if (rows.length === 0) {
            if (messages === '') {
                console.log('留言为空');
                return res.status(200).json({ message: '必须留言！' });
            }
            const sql = 'INSERT INTO users (username, password, name, contact, role, messages) VALUES (?, ?, ?, ?, ?, ?)';
            const values = [username, password, name, contact, role, messages];
            db.query(sql, values, (err, results) => {
                if (err) {
                    console.error('数据插入错误', err.message);
                    return res.status(500).json({ error: 'Server error' });
                } else {
                    console.log('注册成功');
                    return res.status(200).json({ message: '注册成功' });
                }
            });
        } else {
            console.log('账号出错');
            return res.status(400).json({ error: '账号出错' });
        }
    });
});

// 获取留言
app.get('/getmsg', (req, res) => {
    const sql = 'SELECT username, messages FROM users';
    const sqlstr = `
    SELECT users.username, users.messages, msg_favourite.favourite
    FROM users
        INNER JOIN msg_favourite ON users.id = msg_favourite.user_id
        ORDER BY msg_favourite.favourite DESC
        ;`
    db.query(sqlstr, (err, data) => {
        if (err) {
            console.error('数据查找错误：', err.message);
            return res.status(500).json({ error: 'DB error' });
        }
        res.status(200).json({ data });
    });
});

// 删除用户及其相关记录
app.post('/deleteMessages', (req, res) => {
    const { username } = req.body;

    db.getConnection((err, connection) => {
        if (err) {
            console.error('数据库连接错误：', err.message);
            return res.status(500).json({ error: '数据库连接错误' });
        }
        connection.beginTransaction(err => {
            if (err) {
                connection.release();
                console.error('事务开始错误：', err.message);
                return res.status(500).json({ error: '事务开始错误' });
            }
            const getUserID = 'SELECT id FROM users WHERE username = ?';
            connection.query(getUserID, [username], (err, results) => {
                if (err) {
                    return connection.rollback(() => {
                        connection.release();
                        console.error('获取用户ID错误：', err.message);
                        return res.status(500).json({ error: '获取用户ID错误' });
                    });
                }
                if (results.length === 0) {
                    return connection.rollback(() => {
                        connection.release();
                        return res.status(404).json({ message: '用户不存在' });
                    });
                }
                const userId = results[0].id;
                const deleteFavourite = 'DELETE FROM msg_favourite WHERE user_id = ?';
                connection.query(deleteFavourite, [userId], (err, results) => {
                    if (err) {
                        return connection.rollback(() => {
                            connection.release();
                            console.error('删除相关记录错误：', err.message);
                            return res.status(500).json({ error: '删除相关记录错误' });
                        });
                    }
                    const deleteUser = 'DELETE FROM users WHERE id = ?';
                    connection.query(deleteUser, [userId], (err, results) => {
                        if (err) {
                            return connection.rollback(() => {
                                connection.release();
                                console.error('删除用户错误：', err.message);
                                return res.status(500).json({ error: '删除用户错误' });
                            });
                        }
                        connection.commit(err => {
                            if (err) {
                                return connection.rollback(() => {
                                    connection.release();
                                    console.error('事务提交错误：', err.message);
                                    return res.status(500).json({ error: '事务提交错误' });
                                });
                            }
                            connection.release();
                            return res.status(200).json({ message: '用户及相关记录删除成功' });
                        });
                    });
                });
            });
        });
    });
});

// 修改留言
app.post('/editMessages', (req, res) => {
    // 解析前端发来的数据
    const { username, messages } = req.body;
    const sql = 'UPDATE users SET messages = ? WHERE username = ?';
    db.query(sql, [messages, username], (err, rows) => {
        if (err) {
            return res.status(500).json({ error: 'DB error' })
        }
        if (rows.length === 0) {
            res.status(404).json({ message: '没有找到该用户留言，请刷新页面后重试' })
        }
        return res.status(200).json({ message: '修改成功' })
    })
})

// 点赞接口
app.post('/favourite', (req, res) => {
    const { username } = req.body;
    const sql = 'UPDATE msg_favourite JOIN users ON users.id = msg_favourite.user_id SET msg_favourite.favourite = msg_favourite.favourite + 1 WHERE users.username = ?'
    db.query(sql, [username], (err, result) => {
        if (err) {
            console.log(err.message)
            return res.status(500).json({ error: 'DB Error' })
        }
        if (result.affectedRows === 0) {
            return res.status(404).json({ message: '点赞失败，请稍后重试' })
        }
        return res.status(200).json({ message: '点赞成功' })
    })
})

app.listen(5362, () => {
    console.log('server listening on 5362');
});
